package no.aina.skandiabanken;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.provider.BaseColumns;

public class Accounts extends SQLiteOpenHelper {

   private static final String DATABASE_NAME = "skandiabanken.db";
   private static final int DATABASE_VERSION = 5;

   static final String TABLE_NAME_ACCOUNTS = "accounts";
   static final String TABLE_NAME_SETTINGS = "settings";

   // private boolean created;
   // private static boolean hasNew = false;

   private static SQLiteDatabase dbReadable;
   private static SQLiteDatabase dbWritable;

   public Accounts(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   @Override
   public void onCreate(SQLiteDatabase db) {
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ACCOUNTS + " (" + AccountColumns._ID + " INTEGER PRIMARY KEY," + AccountColumns.NAME + " TEXT,"
            + AccountColumns.ACCOUNT_ALIAS + " TEXT," + AccountColumns.AMOUNT + " INTEGER," + AccountColumns.LAST_CHECKED + " TEXT" + ");");

      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SETTINGS + " (" + SettingsColumns._ID + " INTEGER PRIMARY KEY," + SettingsColumns.NAME + " TEXT,"
            + SettingsColumns.VALUE + " TEXT" + ");");

      // hasNew = true;
   }

   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

      Set<Account> accounts = Collections.emptySet();
      Set<Setting> settings = Collections.emptySet();

      // No need to save old accounts in version 5 due to change from number to alias
      // accounts = getAccountsTable(db);
      // settings = getSettingsTable(db);

      db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_ACCOUNTS);
      db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_SETTINGS);

      onCreate(db);

      dbWritable = db;

      saveAccounts(new ArrayList<Account>(accounts));

      for (Setting setting : settings) {
         saveSetting(setting.name, setting.value);
         // hasNew = true;
      }

      // hasNew = true;
      dbWritable = null;
   }

   private Set<Setting> getSettingsTable(SQLiteDatabase db) {

      Set<Setting> settings = new HashSet<Setting>();

      try {

         SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

         qb.setTables(TABLE_NAME_SETTINGS);
         qb.setDistinct(true);

         Cursor c = qb.query(db, null, null, null, null, null, null);

         while (c.moveToNext()) {
            Setting setting = new Setting();
            setting.id = c.getInt(0);
            setting.name = c.getString(1);
            setting.value = c.getString(2);

            settings.add(setting);
         }
         c.close();

      } catch (SQLiteException e) {
      }

      return settings;
   }

   private Set<Account> getAccountsTable(SQLiteDatabase db) {

      Set<Account> accounts = new HashSet<Account>();

      try {
         SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

         qb.setTables(TABLE_NAME_ACCOUNTS);
         qb.setDistinct(true);

         Cursor c = qb.query(db, null, null, null, null, null, null);

         while (c.moveToNext()) {
            Account p = getAccount(c);

            accounts.add(p);
         }
         c.close();

      } catch (SQLiteException e) {
      }

      return accounts;
   }

   // private void emptyAccounts() {
   //
   // hasNew = true;
   //
   // if (dbWritable == null) {
   // dbWritable = getWritableDatabase();
   // }
   //
   // dbWritable.delete(TABLE_NAME_ACCOUNTS, null, null);
   // }

   private void saveAccounts(List<Account> accounts) {

      if (dbWritable == null) {
         dbWritable = getWritableDatabase();
      }

      synchronized (dbWritable) {

         dbWritable.beginTransaction();
         try {

            for (Account p : accounts) {
               // hasNew = true;
               saveAccount(p);
            }

            dbWritable.setTransactionSuccessful();
         } finally {
            if (dbWritable.inTransaction()) {
               dbWritable.endTransaction();
            }
         }
      }

   }

   private Account getAccount(Cursor c) {
      Account p = new Account();
      p.id = c.getInt(0);
      p.name = c.getString(1);
      p.accountAlias = c.getString(2);
      p.amount = c.getInt(3);
      p.lastChecked = c.getString(4);

      return p;
   }

   String getSetting(String name, String defaultValue) {

      String value = defaultValue;

      if (dbReadable == null) {
         dbReadable = getReadableDatabase();
      }

      SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

      qb.setTables(TABLE_NAME_SETTINGS);
      qb.appendWhere(SettingsColumns.NAME + " = '" + name + "'");
      qb.setDistinct(true);

      Cursor c = qb.query(dbReadable, new String[] { SettingsColumns.VALUE }, null, null, null, null, null);

      if (c.moveToNext()) {
         value = c.getString(0);
      }
      c.close();

      return value;
   }

   void saveSetting(String name, String value) {

      if (dbWritable == null) {
         dbWritable = getWritableDatabase();
      }

      ContentValues values = new ContentValues();
      values.put(SettingsColumns.NAME, name);
      values.put(SettingsColumns.VALUE, value);

      if (hasSetting(name)) {
         @SuppressWarnings("unused")
         long id = dbWritable.update(TABLE_NAME_SETTINGS, values, SettingsColumns.NAME + " = ?", new String[] { name });
      } else {
         @SuppressWarnings("unused")
         long id = dbWritable.insert(TABLE_NAME_SETTINGS, null, values);
      }
   }

   private boolean hasSetting(String name) {

      if (dbReadable == null) {
         dbReadable = getReadableDatabase();
      }

      SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

      qb.setTables(TABLE_NAME_SETTINGS);
      qb.appendWhere(SettingsColumns.NAME + " = '" + name + "'");
      qb.setDistinct(true);

      Cursor c = qb.query(dbReadable, null, null, null, null, null, null);

      if (c.moveToNext()) {
         c.close();
         return true;
      }

      c.close();
      return false;
   }

   private class AccountColumns implements BaseColumns {
      private static final String LAST_CHECKED = "lastchecked";
      private static final String AMOUNT = "amount";
      private static final String NAME = "name";
      private static final String ACCOUNT_ALIAS = "accountnumber";

      private AccountColumns() {
      }
   }

   private class SettingsColumns implements BaseColumns {
      private static final String NAME = "name";
      private static final String VALUE = "value";

      private SettingsColumns() {
      }
   }

   private class Setting {

      private String name;
      private String value;
      @SuppressWarnings("unused")
      private int id;

   }

   // private static boolean hasNewAccounts() {
   // return hasNew;
   // }

   Set<Account> getAccounts() {

      if (dbReadable == null) {
         dbReadable = getReadableDatabase();
      }

      Set<Account> accounts = new HashSet<Account>();

      SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

      qb.setTables(TABLE_NAME_ACCOUNTS);

      qb.setDistinct(true);

      Cursor c = qb.query(dbReadable, null, null, null, null, null, null);

      while (c.moveToNext()) {
         Account p = getAccount(c);
         accounts.add(p);
      }
      c.close();

      return accounts;
   }

   // private Account getAccount(int id) throws Exception {
   //
   // if (dbReadable == null) {
   // dbReadable = getReadableDatabase();
   // }
   //
   // SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
   //
   // qb.setTables(TABLE_NAME_ACCOUNTS);
   //
   // qb.appendWhere(AccountColumns._ID + " = " + id + "");
   // qb.setDistinct(true);
   //
   // Cursor c = qb.query(dbReadable, null, null, null, null, null, null);
   //
   // if (c.moveToNext()) {
   //
   // Account p = getAccount(c);
   //
   // c.close();
   //
   // return p;
   // }
   // c.close();
   //
   // throw new Exception("Account not found");
   //
   // }

   @SuppressWarnings("boxing")
   int saveAccount(Account p) {
      ContentValues values = new ContentValues();
      values.put(AccountColumns.NAME, p.name);
      values.put(AccountColumns.ACCOUNT_ALIAS, p.accountAlias);
      values.put(AccountColumns.AMOUNT, p.amount);
      values.put(AccountColumns.LAST_CHECKED, p.lastChecked);

      if (dbWritable == null) {
         dbWritable = getWritableDatabase();
      }

      // hasNew = true;

      int id = (int) dbWritable.insert(TABLE_NAME_ACCOUNTS, null, values);

      return id;
   }

   public void deleteAccount(int id) {

      if (dbWritable == null) {
         dbWritable = getWritableDatabase();
      }

      @SuppressWarnings("unused")
      long deleted = dbWritable.delete(TABLE_NAME_ACCOUNTS, AccountColumns._ID + " = ?", new String[] { "" + id });
   }

}
